| |
974 Search Results Found |
| 1 |
Forum: Data Guard «»
Posted on: Sat, 11 February 2023 11:31 «»
By: Andrey_R
Re: RMAN-06820 on rman backup from standby with OS authentication
…you actually do the test, rather than merely speculating, it may become clear that the datafile backup is 100% useless without the redo required to make it consistent.
That's a fair point. So I did:
- Install Oracle 19c EE, no PDB, on Windows Server…
|
| 2 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 13:32 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…is another possible version with tests of various number values for n.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID…
|
| 3 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 13:30 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…see some problems with that. Please see my tests below using various number values for n, including 1, which returns no rows.
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, …
|
| 4 |
Forum: Text & interMedia «»
Posted on: Thu, 10 August 2023 19:05 «»
By: Barbara Boehmer
Re: How to determine last tiime Content was indexed
…see the following demonstration and answers below.
-- table, initial data, index, additional data for testing:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab
2 (id NUMBER,
3 document VARCHAR2(…
|
| 5 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 08 November 2024 16:24 «»
By: Barbara Boehmer
Re: How to Get the Program Name in 11g version
…may or may not be related to your problem. V$mystat is a synonym for the v_$mystat table.
Privileges must be granted on the table, not the synonym. Please see the example below that demonstrates that.
-- reproduction of your error:
…
|
| 6 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 10 March 2023 13:25 «»
By: Barbara Boehmer
Re: Suggestion on count via sql statement
…method:
SCOTT@orcl_12.1.0.2.0> SELECT Name, TRUNC(TEST_Date) AS Test_date, Instrument, COUNT(*) AS count
2 FROM test
3 WHERE Instrument IN ('Down', 'Not available')
4 GROUP BY Name, TRUNC(TEST_Date), Instrument
5 UNION
6 …
|
| 7 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 05 April 2023 12:25 «»
By: Barbara Boehmer
Re: difficult task
…tables and such look good, although you may want to make changes later.
For your procedure, this is what you have so far.
SCOTT@orcl_12.1.0.2.0> CREATE OR REPLACE PROCEDURE calculate_positions(
2 vInDateTime IN DATE;
3 )
4 AS
5 …
|
| 8 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 09 February 2024 13:17 «»
By: Barbara Boehmer
Re: Xmlattribute not returning tag when the value is null
…am assuming that this is a simplification, and that your data comes from a table with multiple rows and that any value in any column in any row may be null. If this is the case, then after reviewing some of the links from the search link that Michel…
|
| 9 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 08 February 2023 20:27 «»
By: Barbara Boehmer
Re: Convert Date formats
…should be comparing dates as dates, not converting them to character strings and comparing those.
Assuming that you have the following table and data
SCOTT@orcl_12.1.0.2.0> create table mytable (date2 date)
2 /
Table created.
SCOTT@…
|
| 10 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 28 February 2023 10:35 «»
By: Barbara Boehmer
Re: how to incrementally get all numbers incremented by n between 2 columns values
…
SCOTT@orcl_12.1.0.2.0> -- test_data:
SCOTT@orcl_12.1.0.2.0> select * from test_data order by instance_number, snap_id
2 /
INSTANCE_NUMBER SNAP_ID
--------------- ----------
1 165949
1 165950…
|
| 11 |
Forum: Server Administration «»
Posted on: Tue, 24 May 2022 04:08 «»
By: shamsad.khan@gmail.com
Unable to connect to pluggable database - Oracle Datase 12.2.0.1.0
…
I have installed oracle database 12.2.0.1.0. as container database with orclpdb as my pluggabe database
Logged in sqlplus as connect sys as sysdba
ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON=FALSE SCOPE=BOTH;
create user c##test identified by …
|
| 12 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 17 April 2024 16:46 «»
By: Barbara Boehmer
Re: Oracle ORA-00918: column ambiguously defined
…I am trying to understand what causes an error, I try to narrow it down to the simplest example that I can that reproduces the error and the simplest thing that resolves it. Please see the simplified demonstration below in which the problem is …
|
| 13 |
Forum: SQL & PL/SQL «»
Posted on: Sun, 17 December 2023 20:26 «»
By: Barbara Boehmer
Re: Problem in pivot query
…@OraFerro,
I strongly suspect that you have a minor typing error in your query that is not being posted here. You need to post a copy and paste of an actual complete run as I have done below, including the view creation, including line numbers, …
|
| 14 |
Forum: Server Utilities «»
Posted on: Fri, 28 July 2023 12:28 «»
By: Barbara Boehmer
Re: how to parametrize values in pl/sql block
…see the changes to line 34 below. It should work with multiple tables or just one. It will just use an IN clause instead of =.
SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
2 (p_schema in …
|
| 15 |
Forum: Server Utilities «»
Posted on: Fri, 28 July 2023 04:49 «»
By: Barbara Boehmer
Re: how to parametrize values in pl/sql block
…is an example for you.
SCOTT@orcl_12.1.0.2.0> create or replace procedure test_proc
2 (p_schema in varchar2,
3 p_table in varchar2)
4 as
5 l_dp_handle …
|
| 16 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 03 February 2023 15:28 «»
By: Barbara Boehmer
Re: XML parse
…would help to know where your data comes from, how you retrieve it, how it is stored, and what sort of table you want to load it into. In the following I have assumed that it is in a text file on your server. I suggest that you start with the basics and…
|
| 17 |
Forum: Text & interMedia «»
Posted on: Mon, 16 January 2023 11:28 «»
By: Barbara Boehmer
Re: Oracle Text Search - Handling special characters and blank search term
…the translate function, it translates each occurrence of the character in the second parameter to the corresponding character in the third parameter, but just removes others. In the original example, there were only three special characters, so I used …
|
| 18 |
Forum: Text & interMedia «»
Posted on: Tue, 10 January 2023 12:15 «»
By: Barbara Boehmer
Re: Oracle Text Search - Handling special characters and blank search term
…are a number of issues here. One issue is that Oracle Text does not know what to do with the special characters. Another is that if the text query is not executed and there is no score then it does not know what to do when you have selected that …
|
| 19 |
Forum: SQL & PL/SQL «»
Posted on: Sat, 16 December 2023 17:29 «»
By: Barbara Boehmer
Re: Problem in pivot query
…sum(wtd_year) to sum(wtd_amount)
SCOTT@orcl_12.1.0.2.0> select banner from v$version
2 /
BANNER
--------------------------------------------------------------------------------
Oracle Database 12c Enterprise Edition Release 12…
|
| 20 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 18 September 2023 11:41 «»
By: Barbara Boehmer
Re: how to get employee in and out time for generated query based on first in and last out
…the following minimal example, dates with only one punch time will have the same date for in and out times. Also, whatever your first column is "M." (machine?) is ignored. It is ordered by the punch_date, which is really just a …
|
| 21 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 07 February 2023 13:48 «»
By: Barbara Boehmer
Re: XML parse
…are two issues here.
You have multiple LEI under different categories, so those can be given different column names, just as the different Id were named FrId and ToId.
You have multiple Rpt under TradData, so you need an additional XMLTABLE. You …
|
| 22 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 06 November 2023 18:02 «»
By: Barbara Boehmer
Re: Recursive subquery factoring
…following applies a function to one column of your existing view.
-- function and query:
create or replace function get_path_id_post_d
(p_path_id_post in varchar2)
return varchar2
as
v_path_id_post_d &…
|
| 23 |
Forum: SQL & PL/SQL «»
Posted on: Sun, 01 October 2023 03:57 «»
By: Barbara Boehmer
Re: Grouping similar strings together using UTL_MATCH Jaro-Winkler
…following produces the same results as your query, but in a slightly different format. It eliminates the possibility of having things that are not closely related in the same group, due to a chain of relations, by comparing collections and only …
|
| 24 |
Forum: SQL & PL/SQL «»
Posted on: Wed, 29 March 2023 19:55 «»
By: Barbara Boehmer
Re: sql query
…
SCOTT@orcl_12.1.0.2.0> COLUMN mod_flag FORMAT A8
SCOTT@orcl_12.1.0.2.0> -- test data you provided:
SCOTT@orcl_12.1.0.2.0> WITH
2 students (id, term, module) AS
3 (SELECT 123, 2023, 2100 FROM DUAL UNION ALL
4 SELECT 123, …
|
| 25 |
Forum: SQL & PL/SQL «»
Posted on: Fri, 03 March 2023 11:24 «»
By: Barbara Boehmer
Re: Identify Groups where 2 or more Clients have bought the same Products - SQL Oracle
…for creating table and data that you should have provided; please do so yourself next time:
CREATE TABLE table1
(group_id VARCHAR2(8),
client_id VARCHAR2(9),
product_id VARCHAR2(10),
prod_qty NUMBER)
/
INSERT ALL
INTO table1 …
|
| 26 |
Forum: Forms «»
Posted on: Sat, 13 August 2022 03:29 «»
By: sasipalarivattom
Re: Files generated on server with sufix
…Hi,
How are you generating the report?
Have you tried generating report in URL
like http://WIN-SERV:9002/reports/rwservlet?server=rep_server1&report=test.rdf&destype=file&desformat=html&userid=scott/ti ger@orcl&desname=c:\rep1.…
|
| 27 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 11 October 2021 07:51 «»
By: John Watson
Re: Long query with WITH not responding
To test your hypothesis that the parsing is the problem, you can adjust the parameter just for your session:orcl> connect scott/tiger
Connected.
orcl> alter session set "_optimizer_max_permutations"=1000;
Session altered.
orcl>
|
| 28 |
Forum: Networking and Gateways «»
Posted on: Sun, 04 October 2020 22:18 «»
By: pdsQsql
ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)
…Hello,
I am having issue with connecting oracle from Sql Server using Linked Server.
I have installed Oracle client and Sql Server on both the same server.
I have tested TNSPING ORCL coming fine
C:\Windows\system32>TNSPING orcl
TNS Ping …
|
| 29 |
Forum: Networking and Gateways «»
Posted on: Sun, 04 October 2020 12:31 «»
By: Michel Cadot
Re: ORA-12541: TNS:no listener". (.Net SqlClient Data Provider)
…
Welcome to the forum.
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
1/
I doubt your application service name is "orclXDB&…
|
| 30 |
Forum: Networking and Gateways «»
Posted on: Sun, 04 October 2020 12:04 «»
By: pdsQsql
ORA-12541: TNS:no listener". (.Net SqlClient Data Provider) (2 merged)
…Hello,
I am having issue with connecting oracle from Sql Server using Linked Server.
I have installed Oracle client and Sql Server on both the same server.
I have tested TNSPING ORCL coming fine
C:\Windows\system32>TNSPING orcl
TNS Ping …
|
| 31 |
Forum: Client Tools «»
Posted on: Mon, 31 August 2020 10:53 «»
By: rerichards
Connect to Oracle DB using Oracle SQL Developer
…a Windows 10 box I am using using Oracle SQL Developer, attempting to connect to an Oracle DB running Redhat.
The tnsnames.ora file contains the following (the actual has been modified):
orcl =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS…
|
| 32 |
Forum: Text & interMedia «»
Posted on: Tue, 19 May 2020 12:58 «»
By: Barbara Boehmer
Re: ORACLE SQL POSSIBE BUG
…is a default stopword that is ignored and not searched for, unless you specify empty_stoplist in your index creation.
With no stoplist specified using default stoplist:
SCOTT@orcl_12.1.0.2.0> create table test_tab (tt_transvalue varchar2(30…
|
| 33 |
Forum: Text & interMedia «»
Posted on: Thu, 12 September 2019 11:12 «»
By: Barbara Boehmer
Re: how to ignore particular string or character in contains function
…can do this by creating a stoplist, then adding what you want to be ignored to the stoplist, then using that stoplist in the parameters of your context index, as demonstrated below.
-- test environment:
SCOTT@orcl_12.1.0.2.0> SELECT banner FROM v…
|
| 34 |
Forum: Text & interMedia «»
Posted on: Thu, 12 September 2019 10:49 «»
By: Barbara Boehmer
Re: Uncertainty while searching string which contains æøå characters
…should not get results from either query with only the minimal data and search string that you have provided. You need to provide a more complete example, as I have done below, including a copy and paste of your results. You should check to see that …
|
| 35 |
Forum: SQL & PL/SQL «»
Posted on: Thu, 11 April 2019 01:09 «»
By: rzkhan
Re: running balance query
…Case.
Three tables. CUSTOMERS, INVOICE and AMT_RCD
One View. example_view1
create table customers(
customerid number(5),
customername varchar2(60),
opening_balance number(7)
);
insert into customers values(1,'Customer One',200…
|
| 36 |
Forum: Server Utilities «»
Posted on: Thu, 21 February 2019 06:08 «»
By: Barbara Boehmer
Re: SQL Loader for XML File
…xml file names need to either be listed at the end of the control file after BEGINDATA, as in the example below or listed in a separate data file and that data file name would either need to be after INFILE in the control file or in the SQL*Loader command…
|
| 37 |
Forum: SQL & PL/SQL «»
Posted on: Mon, 24 December 2018 05:52 «»
By: Barbara Boehmer
Re: Assign same Group id to rows
…-- table and data for testing:
SCOTT@orcl_12.1.0.2.0> CREATE TABLE test_tab
2 (gold_id NUMBER,
3 rel_gold_id NUMBER,
4 grp_id NUMBER)
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT ALL
2 INTO test_tab (…
|
| 38 |
Forum: SQL & PL/SQL «»
Posted on: Sun, 11 November 2018 05:19 «»
By: Barbara Boehmer
Re: Last and Sum in group by
…following returns the same result as your final query without unnecessary sub-queries.
SCOTT@orcl_12.1.0.2.0> select e.e_id, e.e_date, e.fk_ar_id,
2 max(cc.cc_date) max_cc_date, cc.cc_amount,
3 sum(wth.wth_amount) sum_wth_amount…
|
| 39 |
Forum: Server Utilities «»
Posted on: Thu, 08 November 2018 23:02 «»
By: Barbara Boehmer
Re: Reading data fom file using External Loader
…is an example for you. I am using Windows so I used RECORDS DELIMITED BY '|\r\n'. If you are using Unix it would be RECORDS DELIMITED BY '|\n'. You must also make sure that you specify a large enough maximum size such as CHAR(550) if the field length …
|
| 40 |
Forum: SQL & PL/SQL «»
Posted on: Tue, 23 October 2018 22:23 «»
By: Barbara Boehmer
Re: PLS-00201: identifier 'CTX_DDL' must be declared
…following is just a demonstration of what has already been said, using user test. You will need to substitute your actual user. You can also grant privileges from CTXSYS or from a user with DBA privileges.
-- reproduction of problem:
SCOTT@orcl_12…
|
Blog
Search
Help
Members
Register
Login
Home